﻿using System.Data;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace StudentManagementSystem
{
    public partial class Form1 : Form
    {
        private Database db = new Database();

        public Form1()
        {
            InitializeComponent();
            LoadStudents();
        }

        // 更新学生列表
        private void LoadStudents()
        {
            using (var conn = db.GetConnection())
            {
                string query = "SELECT s.StudentID, s.StudentName, s.Age, c.ClassID, c.ClassName, sch.SchoolID, sch.SchoolName FROM student s JOIN class c ON s.ClassID = c.ClassID JOIN school sch ON c.SchoolID = sch.SchoolID";
                MySqlDataAdapter adapter = new MySqlDataAdapter(query, conn);
                DataTable table = new DataTable();
                adapter.Fill(table);
                dataGridView1.DataSource = table;
            }
        }

        // 添加学生
        private void AddStudents(string name, int age, int classID)
        {
            using (var conn = db.GetConnection())
            {
                string query = "INSERT INTO student (StudentName, Age, ClassID) VALUES (@name, @age, @classID)";
                MySqlCommand command = new MySqlCommand(query, conn);
                command.Parameters.AddWithValue("@name", name);
                command.Parameters.AddWithValue("@age", age);
                command.Parameters.AddWithValue("@classID", classID);

                conn.Open();
                command.ExecuteNonQuery();
                LogAction($"添加学生： {name}");
                LoadStudents();
            }
        }

        // 删除学生
        private void DeleteStudents(int studentID)
        {
            using (var conn = db.GetConnection())
            {
                string query = "DELETE FROM student WHERE StudentID = @studentID";
                MySqlCommand command = new MySqlCommand(query, conn);
                command.Parameters.AddWithValue("@studentID", studentID);

                conn.Open();
                command.ExecuteNonQuery();
                LogAction($"删除学生，id为： {studentID}");
                LoadStudents();
            }
        }

        // 更新学生信息
        private void UpdateStudent(int studentID, string newName, int newAge, int newClass)
        {
            using (var conn = db.GetConnection())
            {
                string query = "UPDATE student SET StudentName = @newName, Age = @newAge, ClassID = @newClass WHERE StudentID = @studentID";
                MySqlCommand command = new MySqlCommand(query, conn);
                command.Parameters.AddWithValue("@newName", newName);
                command.Parameters.AddWithValue("@newAge", newAge);
                command.Parameters.AddWithValue("@newClass", newClass);
                command.Parameters.AddWithValue("@studentID", studentID);

                conn.Open();
                command.ExecuteNonQuery();
                LogAction($"更新学生，id为： {studentID}，名字改为{newName}，年龄改为：{newAge}，班级id改为{newClass}");
                LoadStudents();
            }
        }

        // 更新搜索后学生列表
        private void SearchStudents(int? studentID = null, string studentName = null, int? classID = null, int? schoolID = null)
        {
            using (var conn = db.GetConnection())
            {
                // 基本查询语句
                StringBuilder query = new StringBuilder(
                    "SELECT s.StudentID, s.StudentName, s.Age, c.ClassID, c.ClassName, sch.SchoolID, sch.SchoolName " +
                    "FROM student s " +
                    "JOIN class c ON s.ClassID = c.ClassID " +
                    "JOIN school sch ON c.SchoolID = sch.SchoolID " +
                    "WHERE 1=1"
                );

                // 动态添加条件
                if (studentID.HasValue)
                {
                    query.Append(" AND s.StudentID = @studentID");
                }
                if (!string.IsNullOrWhiteSpace(studentName))
                {
                    query.Append(" AND s.StudentName LIKE @studentName");
                }
                if (classID.HasValue)
                {
                    query.Append(" AND c.ClassID = @classID");
                }
                if (schoolID.HasValue)
                {
                    query.Append(" AND sch.SchoolID = @schoolID");
                }

                MySqlCommand command = new MySqlCommand(query.ToString(), conn);

                // 为参数添加值
                if (studentID.HasValue)
                {
                    command.Parameters.AddWithValue("@studentID", studentID);
                }
                if (!string.IsNullOrWhiteSpace(studentName))
                {
                    command.Parameters.AddWithValue("@studentName", studentName);
                }
                if (classID.HasValue)
                {
                    command.Parameters.AddWithValue("@classID", classID);
                }
                if (schoolID.HasValue)
                {
                    command.Parameters.AddWithValue("@schoolID", schoolID);
                }

                MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                DataTable table = new DataTable();

                // 填充表格并绑定到 DataGridView
                adapter.Fill(table);
                dataGridView1.DataSource = table;
            }
        }


        // 记录用户操作到日志表
        private void LogAction(string action)
        {
            using (var conn = db.GetConnection())
            {
                string query = "INSERT INTO log (Action) VALUES (@action)";
                MySqlCommand command = new MySqlCommand(query, conn);
                command.Parameters.AddWithValue("@action", action);

                conn.Open();
                command.ExecuteNonQuery();
            }
        }

        private void btnAddStudent_Click(object sender, EventArgs e)
        {
            string name = txtStudentName.Text;
            int age = int.Parse(txtStudentAge.Text);
            int classID = int.Parse(txtClassID.Text);
            AddStudents(name, age, classID);
        }

        private void btnDeleteStudent_Click(object sender, EventArgs e)
        {
            int studentID = int.Parse(txtStudentID.Text);
            DeleteStudents(studentID);
        }

        private void btnUpdateStudent_Click(object sender, EventArgs e)
        {
            string newName = txtStudentName.Text;
            int newAge = int.Parse(txtStudentAge.Text);
            int newClass = int.Parse(txtClassID.Text);
            int studentID = int.Parse(txtStudentID.Text);
            UpdateStudent(studentID, newName, newAge, newClass);
        }

        private void btnSearchStudent_Click(object sender, EventArgs e)
        {
            int sid;
            int? studentID = int.TryParse(txtStudentID.Text, out sid) ? sid : (int?)null;
            int? classID = int.TryParse(txtClassID.Text, out sid) ? sid : (int?)null;
            int? schoolID = int.TryParse(txtSchoolID.Text, out sid) ? sid : (int?)null;
            string studentName = txtStudentName.Text;

            SearchStudents(studentID, studentName, classID, schoolID);
        }

        private void btnClassManagement_Click(object sender, EventArgs e)
        {
            Form2 form2 = new Form2();
            form2.ShowDialog();
        }

        private void btnSchoolManagement_Click(object sender, EventArgs e)
        {
            Form3 form3 = new Form3();
            form3.ShowDialog();
        }

        private void btnLog_Click(object sender, EventArgs e)
        {
            Form4 form4 = new Form4();
            form4.ShowDialog();
        }
    }
}
